package com.softer.chengxin.person.dao;

import com.softer.chengxin.common.domin.Common;
import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.person.dao.provider.PersonDaoProvider;
import com.softer.chengxin.person.domin.Person;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/9/26
 */
@Mapper
@Repository
public interface PersonDao {
    /**
     * 查询企业主要人员
     *
     * @param id   企业ID
     * @param duty 职务
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday," +
            "country_code as countryCode,education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone," +
            "political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major,insurance,certificate,id_card_file_id as idCardFileId," +
            "certificate_file_id as certificateFileId,social_security_file_id as socialSecurityFileId,labor_contract_file_id as laborContractFileId,diploma_file_id as diplomaFileId,intermediate_title as intermediateTitle," +
            "title_name as titleName,grade_certificate as gradeCertificate,certificate_name as certificateName  FROM company_person WHERE company_id = #{id} AND duties =#{duty} and state=1 limit 1")
    Person queryMainPersonnel(@Param("id") Integer id, @Param("duty") String duty);

    /**
     * 增加人员
     *
     * @param person 人员实体
     * @return 增加结果
     */
    @Insert("INSERT INTO company_person(gmt_create,gmt_modified ,person_id,project_id ,company_id ,department_id,`name`,sex,birthday,country_code,education_level ," +
            "id_card,photo,phone,political_status,major,insurance,duties,state,certificate,intermediate_title,title_name,grade_certificate,certificate_name)" +
            " VALUES(now(),now(),#{p.personId},#{p.projectId},#{p.companyId},#{p.departmentId},#{p.name},#{p.sex},#{p.birth},#{p.countryCode},#{p.educationLevel},#{p.idCard},#{p.photo}," +
            "#{p.phone},#{p.politicalStatus},#{p.major},#{p.insurance},#{p.duties},1,#{p.certificate},#{p.intermediateTitle},#{p.titleName},#{p.gradeCertificate},#{p.certificateName})")
    int addPerson(@Param("p") Person person);

    /**
     * 判断该身份证是否存在
     *
     * @param idCard 身份证
     * @return 身份证
     */
    @Select("SELECT id_card FROM company_person WHERE id_card=#{idCard} and state=1 limit 1")
    String selectPerson(@Param("idCard") String idCard);

    /**
     * 根据身份证查询人员
     *
     * @param idCard 身份证
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties as dutiesCode,(SELECT name FROM dic_info WHERE code = duties) as duties,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday,country_code as countryCode,education_level as educationLevelCode," +
            "(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone,political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major," +
            "insurance,certificate,intermediate_title as intermediateTitle,title_name as titleName,grade_certificate as gradeCertificate,certificate_name as certificateName FROM company_person WHERE id_card = #{id} and state=1 limit 1")
    Person getPersonByIdCard(@Param("id") String idCard);

    /**
     * 判断该企业下是否存在该身份证
     *
     * @param idCard 身份证
     * @param id     企业ID
     * @return 身份证
     */
    @Select("SELECT id_card FROM company_person WHERE id_card=#{idCard} and state=1 and company_id=#{id} limit 1")
    String checkCorpPerson(@Param("idCard") String idCard, @Param("id") String id);

    /**
     * 判断身份证的职务
     *
     * @param idCard 身份证
     * @return 职务
     */
    @Select("SELECT duties AS dutiesCode,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS duties FROM company_person WHERE id_card=#{idCard} and state=1 limit 1")
    String checkDuty(@Param("idCard") String idCard);


    /**
     * 更新物业人员信息编辑
     *
     * @param person 人员实体
     * @return 修改是否成功
     */
    @Update("update company_person set `name`=#{p.name},sex=#{p.sex},country_code=#{p.countryCode},birthday=#{p.birth},education_level=#{p.educationLevel},id_card=#{p.idCard},photo=#{p.photo}," +
            "political_status=#{p.politicalStatus},phone=#{p.phone},department_id=#{p.departmentId},major=#{p.major},insurance=#{p.insurance},duties=#{p.duties},certificate=#{p.certificate}," +
            "intermediate_title=#{p.intermediateTitle},title_name=#{p.titleName},grade_certificate=#{p.gradeCertificate},certificate_name=#{p.certificateName},flag=0 where id=#{p.id}")
    int updatePerson(@Param("p") Person person);

    /**
     * 企业从业人员删除时更改企业状态
     *
     * @param id 从业人员id
     * @return 修改是否成功
     */
    @Update("update company_base_info b LEFT JOIN company_person p on b.id=p.company_id set b.state=0 where p.id=#{id}")
    int updateCompanyState(@Param("id") Integer id);


    /**
     * /**
     * 更新企业状态
     *
     * @param person 人员实体
     * @return 修改是否成功
     */
    @Update("update company_base_info b  set b.state=0 where b.id=#{p.companyId}")
    int updateCompany(@Param("p") Person person);


    /**
     * 更新项目状态
     *
     * @param person 人员实体
     * @return 修改是否成功
     */
    @Update("update company_project b set b.state=0 where b.id=#{p.projectId}")
    int updateProjectState(@Param("p") Person person);

    /**
     * 项目人员信息编辑
     *
     * @param person 项目人员实体
     * @return 修改是否成功
     */
    @Update("update company_person set `name`=#{p.name},sex=#{p.sex},country_code=#{p.countryCode},project_id =#{p.projectId},birthday=#{p.birth},education_level=#{p.educationLevel},id_card=#{p.idCard},photo=#{p.photo}," +
            "political_status=#{p.politicalStatus},phone=#{p.phone},department_id=#{p.departmentId},person_id=#{p.personId},major=#{p.major},insurance=#{p.insurance},duties=#{p.duties},flag=0," +
            "certificate=#{p.certificate},intermediate_title=#{p.intermediateTitle},title_name=#{p.titleName},grade_certificate=#{p.gradeCertificate},certificate_name=#{certificateName} where id=#{p.id}")
    int updateProjectPerson(@Param("p") Person person);

    /**
     * 查询企业从业人员信息数量
     *
     * @param id           企业ID
     * @param name         人员名字
     * @param departmentId 部门ID
     * @return 人员数量
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryPersonListNum")
    int queryPersonListNum(@Param("id") Integer id, @Param("name") String name,
                           @Param("departmentId") Integer departmentId);

    /**
     * 分页查询企业人员列表
     *
     * @param page         页面信息
     * @param id           企业ID
     * @param name         人员名字
     * @param departmentId 部门ID
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryPersonList")
    List<Person> queryPersonList(@Param("page") Page page, @Param("id") Integer id,
                                 @Param("name") String name, @Param("departmentId") Integer departmentId);

    /**
     * 根据Id查询人员信息
     *
     * @param id 人员ID
     * @return 人员信息
     */
    @Select("SELECT id,DATE_FORMAT(gmt_create, '%Y-%m-%d') AS gmtCreate,DATE_FORMAT(gmt_modified, '%Y-%m-%d') AS gmtModified,person_id AS personId,duties,( SELECT NAME FROM dic_info WHERE CODE = duties ) AS dutiesCode,state," +
            "project_id as projectId,company_id as companyId,department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = department_id) as departmentId,`name`,sex,birthday,country_code as countryCode,education_level as educationLevelCode," +
            "(SELECT name FROM dic_info WHERE code = education_level) AS educationLevel,id_card as idCard,photo,phone,political_status as politicalStatus,major AS majorCode,(SELECT name FROM dic_info WHERE code = major) AS major, insurance," +
            "certificate,intermediate_title as intermediateTitle,title_name as titleName,grade_certificate as gradeCertificate,certificate_name as certificateName FROM company_person WHERE id = #{id}")
    Person queryPersonById(@Param("id") Integer id);

    /**
     * 刪除人员（将人员状态设为离职,离职状态设置为0）
     *
     * @param id 人员Id
     * @return 处理结果
     */
    @Delete("delete from company_person WHERE id=#{id}")
    int delete(@Param("id") Integer id);

    /**
     * 彻底删除从业人员
     *
     * @param id 人员ID
     * @return 处理结果
     */
    @Delete("DELETE FROM company_person WHERE id=#{id}")
    int deletePerson(@Param("id") Integer id);

    /**
     * 删除项目人员
     *
     * @param id 人员ID
     * @return 处理结果
     */
    @Update("UPDATE company_person SET project_id=0 WHERE id=#{id}")
    int updatePersonProject(@Param("id") Integer id);

    /**
     * 人员调动
     *
     * @param id        人员ID
     * @param projectId 项目ID
     * @return 调动结果
     */
    @Update("UPDATE company_person SET project_id=#{projectId},gmt_modified=now() WHERE id=#{id}")
    int mobilize(@Param("id") Integer id, @Param(Common.PROJECT_ID) Integer projectId);

    /**
     * 删除项目时，删除项目中的人员
     *
     * @param projectId 项目ID
     * @return 删除数量
     */
    @Update("delete from company_person WHERE project_id=#{projectId}")
    int deleteProjectPerson(@Param(Common.PROJECT_ID) Integer projectId);

    /**
     * 查询企业所有人员，用于下拉列表
     *
     * @param name 姓名，用于模糊查询
     * @param id   企业ID
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryAllPerson")
    List<Person> queryAllPerson(@Param("name") String name, @Param("id") Integer id);

    /**
     * 查询项目人员数量
     *
     * @param person 查询实体
     * @return 人员数量
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryProjectPersonNum")
    int queryProjectPersonNum(@Param("person") Person person);

    /**
     * 查询项目人员列表
     *
     * @param page   页面实体
     * @param person 查询实体
     * @return 人员列表
     */
    @SelectProvider(type = PersonDaoProvider.class, method = "queryProjectPersonList")
    List<Person> queryProjectPersonList(@Param("page") Page page, @Param("person") Person person);


}
